/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.comut.crud.dao;

import com.comut.crud.pojo.fleet.Fleet;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import java.sql.*;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class FleetDao {
    

    private static Logger _logger = Logger.getLogger(FleetDao.class.getName());
    private Connection _moovefleetConnection;
    private Connection _ifleetConnection;

    
    public FleetDao(Connection moovefleetConnection, Connection ifleetConnection) {
        this._moovefleetConnection = moovefleetConnection;
        this._ifleetConnection = ifleetConnection;
    }

    public ResultSet getFleet(int id) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getFleet](?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getFleet error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    public int createFleet(Fleet fleet) {

        int toRet = -1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createFleet](?,?,?)}";
        try {

            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setString(1, fleet.getAlias());
            stmt.setString(2, fleet.getId_channel());
            stmt.setString(3, null);  
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("MooveFleet createFleet error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("MooveFleet createFleet error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        
        try {

            stmt = _ifleetConnection.prepareCall(query);
            stmt.setString(1, fleet.getAlias());
            stmt.setString(2, fleet.getId_channel());
            stmt.setInt(3, toRet);  
            rs = stmt.executeQuery();
           
        } catch (SQLException ex) {
            _logger.error("IFleet createFleet error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("IFleet createFleet error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;
    }

    /**
     * Update the fleet table in SQLServer
     *
     * @param fleet Object with the where clause and the update clause
     */
    public void updateFleet(Fleet fleet) {

        String update = "UPDATE [conexa_europa_q108].[dbo].[fleet]  SET [ALIAS] ='"+fleet.getAlias()+"'";
        String where = " WHERE [id_fleet] = "+fleet.getId();

        PreparedStatement stmt = null;
        try {
            stmt = _moovefleetConnection.prepareStatement(update + where);
            int updateRows = stmt.executeUpdate();
            if (updateRows == 0) {
                throw new ObjectNotFoundException("MooveFleet the fleet does not exist", ErrorCodes.OBJECT_NOT_FOUND);
            }
        } catch (SQLException ex) {
            _logger.fatal("MooveFleet updateFleet error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        try {
            stmt = _ifleetConnection.prepareStatement(update + where);
            int updateRows = stmt.executeUpdate();
            if (updateRows == 0) {
                throw new ObjectNotFoundException("IFleet the fleet does not exist", ErrorCodes.OBJECT_NOT_FOUND);
            }
        } catch (SQLException ex) {
            _logger.fatal("IFleet updateFleet error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

    }

    public void deleteFleet(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteFleet](?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("MooveFleet deleteFleet error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        try {
            stmt = _ifleetConnection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("iFleet deleteFleet error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    
    /**
     * check if the fleet is associated with vehicles
     * if yes throw an exception
     * @param id_fleet
     * @param alias 
     */
    public void checkAssociateWithVehicles(int id_fleet) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getMobileAssociated](?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);            
            stmt.setInt(1, id_fleet);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The fleet has vehicles associated", ErrorCodes.VEHICLES_ASSOCIATED);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkAssociateWithVehicles error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    /**
     * check if there is other fleet with the Alias,
     * if exists throw an exception
     * @param id_fleet
     * @param alias 
     */
    public void checkUniqueAlias(int id_fleet, String alias) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueFleetAlias](?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            if(id_fleet > 0)
                stmt.setInt(1, id_fleet);
            else
                stmt.setObject(1, null);
            stmt.setString(2, alias);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The Alias already exists", ErrorCodes.ALIAS_FOUND);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkUniqueAlias error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
}
